package com.aps.service.excel;

import com.baomidou.mybatisplus.annotation.TableName;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.lang.reflect.Field;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.util.*;
import java.util.stream.Collectors;

/**
 * Excel导入服务
 * 支持基于实体映射的动态导入功能
 */
@Service
public class ExcelImportService {
    
    private final Map<String, ExcelImportStrategy> strategies = new HashMap<>();
    private final ExcelValidationService validationService;
    
    public ExcelImportService(ExcelValidationService validationService) {
        this.validationService = validationService;
        initializeStrategies();
    }
    
    /**
     * 初始化导入策略
     */
    private void initializeStrategies() {
        // 注册默认策略
        strategies.put("workOrder", new WorkOrderImportStrategy());
        strategies.put("device", new DeviceImportStrategy());
        // 可扩展更多策略
    }
    
    /**
     * 导入Excel数据
     */
    public ExcelImportResult importExcel(MultipartFile file, String entityName, Map<String, Object> options) {
        try {
            // 获取导入策略
            ExcelImportStrategy strategy = getStrategy(entityName);
            
            // 解析Excel文件
            Workbook workbook = new XSSFWorkbook(file.getInputStream());
            Sheet sheet = workbook.getSheetAt(0);
            
            // 获取实体类
            Class<?> entityClass = strategy.getEntityClass();
            
            // 解析表头映射
            Map<String, String> headerMapping = parseHeaderMapping(sheet, entityClass);
            
            // 读取数据
            List<Map<String, Object>> rawData = readDataFromSheet(sheet, headerMapping);
            
            // 验证数据
            List<ExcelValidationError> validationErrors = validationService.validateData(rawData, entityClass, strategy);
            
            // 转换并保存数据
            ExcelImportResult result = new ExcelImportResult();
            if (validationErrors.isEmpty()) {
                List<Object> entities = convertToEntities(rawData, entityClass, headerMapping);
                int successCount = strategy.saveEntities(entities);
                result.setSuccess(true);
                result.setSuccessCount(successCount);
                result.setMessage("导入成功");
            } else {
                result.setSuccess(false);
                result.setFailCount(validationErrors.size());
                result.setErrors(validationErrors);
                result.setMessage("数据验证失败");
            }
            
            workbook.close();
            return result;
            
        } catch (Exception e) {
            ExcelImportResult result = new ExcelImportResult();
            result.setSuccess(false);
            result.setMessage("导入失败: " + e.getMessage());
            return result;
        }
    }
    
    /**
     * 生成Excel模板
     */
    public byte[] generateTemplate(String entityName, List<ExcelColumnConfig> columns) {
        try {
            ExcelImportStrategy strategy = getStrategy(entityName);
            Class<?> entityClass = strategy.getEntityClass();
            
            Workbook workbook = new XSSFWorkbook();
            Sheet sheet = workbook.createSheet("数据模板");
            
            // 创建样式
            CellStyle headerStyle = createHeaderStyle(workbook);
            CellStyle dataStyle = createDataStyle(workbook);
            
            // 创建表头
            Row headerRow = sheet.createRow(0);
            for (int i = 0; i < columns.size(); i++) {
                Cell cell = headerRow.createCell(i);
                cell.setCellValue(columns.get(i).getLabel());
                cell.setCellStyle(headerStyle);
                sheet.setColumnWidth(i, 4000);
            }
            
            // 添加示例数据
            addSampleData(sheet, columns, dataStyle);
            
            // 转换为字节数组
            java.io.ByteArrayOutputStream outputStream = new java.io.ByteArrayOutputStream();
            workbook.write(outputStream);
            workbook.close();
            
            return outputStream.toByteArray();
            
        } catch (Exception e) {
            throw new RuntimeException("生成模板失败: " + e.getMessage(), e);
        }
    }
    
    /**
     * 解析表头映射关系
     */
    private Map<String, String> parseHeaderMapping(Sheet sheet, Class<?> entityClass) {
        Map<String, String> mapping = new HashMap<>();
        Row headerRow = sheet.getRow(0);
        
        if (headerRow == null) {
            throw new RuntimeException("Excel文件格式错误：缺少表头行");
        }
        
        // 获取实体字段映射
        Map<String, String> fieldMapping = getFieldMapping(entityClass);
        
        for (Cell cell : headerRow) {
            String headerValue = cell.getStringCellValue().trim();
            String fieldName = fieldMapping.get(headerValue);
            if (fieldName != null) {
                mapping.put(String.valueOf(cell.getColumnIndex()), fieldName);
            }
        }
        
        return mapping;
    }
    
    /**
     * 获取实体字段映射
     */
    private Map<String, String> getFieldMapping(Class<?> entityClass) {
        Map<String, String> mapping = new HashMap<>();
        Field[] fields = entityClass.getDeclaredFields();
        
        for (Field field : fields) {
            ExcelColumn annotation = field.getAnnotation(ExcelColumn.class);
            if (annotation != null) {
                mapping.put(annotation.value(), field.getName());
            } else {
                // 默认使用字段名
                mapping.put(field.getName(), field.getName());
            }
        }
        
        return mapping;
    }
    
    /**
     * 从Sheet中读取数据
     */
    private List<Map<String, Object>> readDataFromSheet(Sheet sheet, Map<String, String> headerMapping) {
        List<Map<String, Object>> dataList = new ArrayList<>();
        
        for (int i = 1; i <= sheet.getLastRowNum(); i++) {
            Row row = sheet.getRow(i);
            if (row == null) continue;
            
            Map<String, Object> rowData = new HashMap<>();
            rowData.put("_rowIndex", i + 1); // 行号（从1开始）
            
            for (Map.Entry<String, String> entry : headerMapping.entrySet()) {
                int colIndex = Integer.parseInt(entry.getKey());
                String fieldName = entry.getValue();
                
                Cell cell = row.getCell(colIndex);
                Object value = getCellValue(cell);
                rowData.put(fieldName, value);
            }
            
            dataList.add(rowData);
        }
        
        return dataList;
    }
    
    /**
     * 获取单元格值
     */
    private Object getCellValue(Cell cell) {
        if (cell == null) return null;
        
        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue().trim();
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    return cell.getDateCellValue();
                }
                return cell.getNumericCellValue();
            case BOOLEAN:
                return cell.getBooleanCellValue();
            case FORMULA:
                return cell.getCellFormula();
            default:
                return null;
        }
    }
    
    /**
     * 转换为实体对象
     */
    private List<Object> convertToEntities(List<Map<String, Object>> rawData, Class<?> entityClass, Map<String, String> headerMapping) {
        return rawData.stream().map(data -> {
            try {
                Object entity = entityClass.getDeclaredConstructor().newInstance();
                
                for (Map.Entry<String, Object> entry : data.entrySet()) {
                    if ("_rowIndex".equals(entry.getKey())) continue;
                    
                    String fieldName = entry.getKey();
                    Object value = entry.getValue();
                    
                    if (value != null) {
                        Field field = entityClass.getDeclaredField(fieldName);
                        field.setAccessible(true);
                        Object convertedValue = convertValue(value, field.getType());
                        field.set(entity, convertedValue);
                    }
                }
                
                return entity;
            } catch (Exception e) {
                throw new RuntimeException("转换实体对象失败: " + e.getMessage(), e);
            }
        }).collect(Collectors.toList());
    }
    
    /**
     * 值类型转换
     */
    private Object convertValue(Object value, Class<?> targetType) {
        if (value == null) return null;
        if (targetType.isAssignableFrom(value.getClass())) return value;
        
        String strValue = value.toString().trim();
        
        if (targetType == String.class) {
            return strValue;
        } else if (targetType == Integer.class || targetType == int.class) {
            return Double.valueOf(strValue).intValue();
        } else if (targetType == Long.class || targetType == long.class) {
            return Double.valueOf(strValue).longValue();
        } else if (targetType == Double.class || targetType == double.class) {
            return Double.valueOf(strValue);
        } else if (targetType == Boolean.class || targetType == boolean.class) {
            return Boolean.valueOf(strValue);
        } else if (targetType == LocalDate.class) {
            if (value instanceof Date) {
                return ((Date) value).toInstant().atZone(java.time.ZoneId.systemDefault()).toLocalDate();
            }
            return LocalDate.parse(strValue);
        } else if (targetType == LocalDateTime.class) {
            if (value instanceof Date) {
                return ((Date) value).toInstant().atZone(java.time.ZoneId.systemDefault()).toLocalDateTime();
            }
            return LocalDateTime.parse(strValue);
        }
        
        return value;
    }
    
    /**
     * 创建表头样式
     */
    private CellStyle createHeaderStyle(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();
        style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        style.setBorderTop(BorderStyle.THIN);
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        
        Font font = workbook.createFont();
        font.setBold(true);
        font.setFontHeightInPoints((short) 12);
        style.setFont(font);
        
        return style;
    }
    
    /**
     * 创建数据样式
     */
    private CellStyle createDataStyle(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();
        style.setBorderTop(BorderStyle.THIN);
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        style.setAlignment(HorizontalAlignment.LEFT);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        
        return style;
    }
    
    /**
     * 添加示例数据
     */
    private void addSampleData(Sheet sheet, List<ExcelColumnConfig> columns, CellStyle dataStyle) {
        // 添加一行示例数据
        Row sampleRow = sheet.createRow(1);
        for (int i = 0; i < columns.size(); i++) {
            Cell cell = sampleRow.createCell(i);
            cell.setCellValue(columns.get(i).getSampleValue());
            cell.setCellStyle(dataStyle);
        }
    }
    
    /**
     * 获取导入策略
     */
    private ExcelImportStrategy getStrategy(String entityName) {
        ExcelImportStrategy strategy = strategies.get(entityName);
        if (strategy == null) {
            throw new RuntimeException("不支持的实体类型: " + entityName);
        }
        return strategy;
    }
}

/**
 * Excel导入结果
 */
class ExcelImportResult {
    private boolean success;
    private int successCount;
    private int failCount;
    private String message;
    private List<ExcelValidationError> errors = new ArrayList<>();
    
    // getters and setters
    public boolean isSuccess() { return success; }
    public void setSuccess(boolean success) { this.success = success; }
    
    public int getSuccessCount() { return successCount; }
    public void setSuccessCount(int successCount) { this.successCount = successCount; }
    
    public int getFailCount() { return failCount; }
    public void setFailCount(int failCount) { this.failCount = failCount; }
    
    public String getMessage() { return message; }
    public void setMessage(String message) { this.message = message; }
    
    public List<ExcelValidationError> getErrors() { return errors; }
    public void setErrors(List<ExcelValidationError> errors) { this.errors = errors; }
}

/**
 * Excel列配置
 */
class ExcelColumnConfig {
    private String field;        // 字段名
    private String label;        // 列标题
    private String sampleValue;  // 示例值
    private boolean required;    // 是否必填
    private Class<?> type;       // 数据类型
    
    public ExcelColumnConfig(String field, String label, String sampleValue, boolean required, Class<?> type) {
        this.field = field;
        this.label = label;
        this.sampleValue = sampleValue;
        this.required = required;
        this.type = type;
    }
    
    // getters and setters
    public String getField() { return field; }
    public void setField(String field) { this.field = field; }
    
    public String getLabel() { return label; }
    public void setLabel(String label) { this.label = label; }
    
    public String getSampleValue() { return sampleValue; }
    public void setSampleValue(String sampleValue) { this.sampleValue = sampleValue; }
    
    public boolean isRequired() { return required; }
    public void setRequired(boolean required) { this.required = required; }
    
    public Class<?> getType() { return type; }
    public void setType(Class<?> type) { this.type = type; }
}

/**
 * Excel列注解
 */
@java.lang.annotation.Retention(java.lang.annotation.RetentionPolicy.RUNTIME)
@java.lang.annotation.Target(java.lang.annotation.ElementType.FIELD)
@interface ExcelColumn {
    String value(); // 列名
    boolean required() default false; // 是否必填
    String format() default ""; // 格式化
}
